package com.huabo.sql.builder;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

import java.util.ArrayList;
import java.util.List;


/**
 * <p>ClassName: WhereBuilder</p>
 * <p>Description: 组织sql查询语句的where条件</p>
 * <p>Author: Jackie</p>
 * <p>Date: 2013年11月9日</p>
 */
public class WhereBuilder {

    private static final Logger log = Logger.getLogger(WhereBuilder.class);

    private StringBuilder whereBuilder;
    private String orderBy;
    private String groupBy;
    private int pageNum = 0;//当前页数
    private int pageSize = 0;//每页记录数
    private List<Condition> currentList;
    private List<Group> arrayList;
    private List<CustomSQL> customSQLList;

    private static final String AND = " AND ";
    private static final String OR = " OR ";

    /**
     * <p>Description: 构造函数，or条件不带括号</p>
     */
    public WhereBuilder() {
        currentList = new ArrayList<Condition>();
        arrayList = new ArrayList<Group>();
        arrayList.add(new Group(currentList));
    }

    /**
     * <p>Description: 构造函数</p>
     *
     * @param isGroup 条件是否带括号
     */
    public WhereBuilder(boolean isGroup) {
        currentList = new ArrayList<Condition>();
        arrayList = new ArrayList<Group>();
        arrayList.add(new Group(currentList, isGroup, ""));
    }

    private boolean isEmpty(String field, Object value) {
        if (field == null || "".equals(field)
                || value == null || "".equals(value))
            return true;
        return false;
    }

    private boolean isEmpty(String field) {
        if (field == null || "".equals(field))
            return true;
        return false;
    }

    /**
     * <p>Description: 清空所有条件</p>
     */
    public void clearAll(boolean isGroup) {
        clear(isGroup);
        removeCustomSQL();
        orderBy = null;
        groupBy = null;
        pageNum = 0;
        pageSize = 0;
    }

    /**
     * <p>Description: 清空查询条件</p>
     *
     * @param isGroup 条件是否带括号
     */
    public void clear(boolean isGroup) {
        currentList.clear();
        arrayList.clear();
        arrayList.add(new Group(currentList, isGroup, ""));
        if (customSQLList != null) {
            customSQLList.clear();
        }
    }

    /**
     * 清空自定义SQL
     *
     * @return this
     */
    public WhereBuilder removeCustomSQL() {
        if (customSQLList != null) {
            customSQLList.clear();
        }
        return this;
    }

    /**
     * <p>Description: 添加条件</p>
     *
     * @param field    字段
     * @param clause   判断字符
     * @param value    值
     * @param isField  value是否是字段值
     * @param isIndex  caluse是否是索引操作符
     * @param operator 操作符 and、or
     */
    private void addCondition(String field, Object value, String clause, boolean isField, boolean isIndex, String operator) {
        if (isEmpty(field, value))
            return;

        Condition con = new Condition(field.trim(), value, clause, isField, isIndex, operator);
        currentList.add(con);
    }

    /**
     * 添加自定义SQL
     *
     * @param operator 操作符，and、or
     * @param sql      自定义SQL值
     * @param values   占位符对应值
     */
    private void addCustomSQL(String operator, String sql, Object[] values) {
        if (StringUtils.isEmpty(sql)) {
            return;
        }

        CustomSQL customSQL = new CustomSQL(operator, sql, values);
        if (customSQLList == null) {
            customSQLList = new ArrayList<CustomSQL>();
        }
        customSQLList.add(customSQL);
    }

    /**
     * <p>Description: 增加括号分组</p>
     *
     * @return this
     */
    public WhereBuilder andGroup() {
        currentList = new ArrayList<Condition>();
        arrayList.add(new Group(currentList, true, AND));
        return this;
    }

    /**
     * <p>Description: 增加括号分组</p>
     *
     * @return this
     */
    public WhereBuilder orGroup() {
        currentList = new ArrayList<Condition>();
        arrayList.add(new Group(currentList, true, OR));
        return this;
    }

    /**
     * <p>Description: 去除括号分组</p>
     *
     * @return this
     */
    public WhereBuilder noGroupWithAnd() {
        currentList = new ArrayList<Condition>();
        arrayList.add(new Group(currentList, false, AND));
        return this;
    }

    /**
     * <p>Description: 去除括号分组</p>
     *
     * @return this
     */
    public WhereBuilder noGroupWithOr() {
        currentList = new ArrayList<Condition>();
        arrayList.add(new Group(currentList, false, OR));
        return this;
    }

    @SuppressWarnings("rawtypes")
    private void generateGroup(List<Condition> list) {
        for (int i = 0, size = list.size(); i < size; i++) {
            Condition con = list.get(i);
            if (i != 0) {
                whereBuilder.append(con.operator);
            }
            whereBuilder.append(con.field);
            String clause = con.clause;

            if ("IS NULL".equals(clause) || "IS NOT NULL".equals(clause)) {
                whereBuilder.append(" ").append(clause);
                continue;
            }

            Object value = con.value;
            if (clause.equals("IN") || clause.equals("NOT IN")) {
                whereBuilder.append(" ").append(clause).append(" (");
                Object[] array = (Object[]) value;
                for (int j = 0; j < array.length; j++) {
                    whereBuilder.append("?");
                    if (j != array.length - 1) {
                        whereBuilder.append(", ");
                    }
                }
                whereBuilder.append(")");
                continue;
            }

            whereBuilder.append(" ").append(clause).append(" ?");
        }
    }

    /**
     * <p>Description: 获取where拼接字符串</p>
     *
     * @return String
     */
    private String getCondition() {
        whereBuilder = new StringBuilder("");
        int size = arrayList.size();
        boolean isStart = true;
        for (int i = 0; i < size; i++) {
            Group group = arrayList.get(i);
            if (group.list.isEmpty())
                continue;

            if (!isStart) {
                whereBuilder.append(group.operator);
            }
            if (isStart) {
                isStart = false;
            }
            if (group.isWithBracket) {
                whereBuilder.append("(");
                generateGroup(group.list);
                whereBuilder.append(")");
            } else {
                generateGroup(group.list);
            }
        }

        if (customSQLList != null) {
            for (int i = 0, length = customSQLList.size(); i < length; i++) {
                CustomSQL customSQL = customSQLList.get(i);
                if (whereBuilder.length() > 0) {
                    whereBuilder.append(customSQL.operator);
                }
                whereBuilder.append(" ").append(customSQL.sql);
            }
        }

        String whereSql = whereBuilder.toString().trim();
        return whereSql;
    }

    public String getSql(String fromSql) {
        String whereSql = getCondition();

        StringBuilder sb = new StringBuilder(fromSql);
        if (!isEmpty(whereSql)) {
            sb.append(" WHERE ").append(whereSql);
        }

        if (!isEmpty(groupBy)) {
            sb.append(" GROUP BY ").append(groupBy);
        }
        if (!isEmpty(orderBy)) {
            sb.append(" ORDER BY ").append(orderBy);
        }

        return sb.toString();
    }

    public String getCountSql(String fromSql) {
        String whereSql = getCondition();

        StringBuilder sb = new StringBuilder(fromSql);
        if (!isEmpty(whereSql)) {
            sb.append(" WHERE ").append(whereSql);
        }

        return sb.toString();
    }

    @SuppressWarnings("rawtypes")
    public Object[] getParameters() {
        List<Object> paramList = new ArrayList<Object>();
        int size = arrayList.size();
        for (int i = 0; i < size; i++) {
            Group group = arrayList.get(i);
            if (group.list.isEmpty())
                continue;

            List<Condition> list = group.list;
            for (int k = 0, length = list.size(); k < length; k++) {
                Condition con = list.get(k);
                if (!con.isIndex) {
                    continue;
                }

                String clause = con.clause;

                if ("IN".equals(clause) || "NOT IN".equals(clause)) {
                    Object[] array = (Object[]) con.value;
                    for (int j = 0; j < array.length; j++) {
                        paramList.add(array[j]);
                    }
                    continue;
                }
                if (log.isDebugEnabled()) {
                    log.debug(String.format("sql param name = %s value = %s", con.field, con.value.toString()));
                }
                paramList.add(con.value);
            }
        }

        if (customSQLList != null) {
            for (int i = 0, length = customSQLList.size(); i < length; i++) {
                CustomSQL customSQL = customSQLList.get(i);
                if (customSQL.values != null) {
                    Object[] values = customSQL.values;
                    for (int k = 0; k < values.length; k++) {
                        if (log.isDebugEnabled()) {
                            log.debug(String.format("custom param value = %s", values[k].toString()));
                        }
                        paramList.add(values[k]);
                    }
                }
            }
        }

        if (paramList.isEmpty()) {
            return null;
        }
        return paramList.toArray(new Object[paramList.size()]);
    }

    /**
     * <p>Description: is null条件</p>
     *
     * @param field 字段
     * @return this
     */
    public WhereBuilder andIsNull(String field) {
        if (field == null || "".equals(field))
            return this;

        Condition con = new Condition(field.trim(), "", "IS NULL", false, false, AND);
        currentList.add(con);
        return this;
    }

    /**
     * <p>Description: is null条件</p>
     *
     * @param field 字段
     * @return this
     */
    public WhereBuilder orIsNull(String field) {
        if (field == null || "".equals(field))
            return this;

        Condition con = new Condition(field.trim(), "", "IS NULL", false, false, OR);
        currentList.add(con);
        return this;
    }

    /**
     * <p>Description: is not null条件</p>
     *
     * @param field 字段
     * @return this
     */
    public WhereBuilder andIsNotNull(String field) {
        if (field == null || "".equals(field))
            return this;

        Condition con = new Condition(field.trim(), "", "IS NOT NULL", false, false, AND);
        currentList.add(con);
        return this;
    }

    /**
     * <p>Description: is not null条件</p>
     *
     * @param field 字段
     * @return this
     */
    public WhereBuilder orIsNotNull(String field) {
        if (field == null || "".equals(field))
            return this;

        Condition con = new Condition(field.trim(), "", "IS NOT NULL", false, false, OR);
        currentList.add(con);
        return this;
    }

    /**
     * <p>Description: 等于（=）</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public WhereBuilder andEquals(String field, Object value) {
        addCondition(field, value, "=", false, true, AND);
        return this;
    }

    /**
     * <p>Description: 等于（=）</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public WhereBuilder orEquals(String field, Object value) {
        addCondition(field, value, "=", false, true, OR);
        return this;
    }

    /**
     * <p>Description: 不等于（<>）</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public WhereBuilder andNotEquals(String field, Object value) {
        addCondition(field, value, "<>", false, true, AND);
        return this;
    }

    /**
     * <p>Description: 不等于（<>）</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public WhereBuilder orNotEquals(String field, Object value) {
        addCondition(field, value, "<>", false, true, OR);
        return this;
    }

    /**
     * <p>Description: 大于（>）</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public WhereBuilder andGreaterThan(String field, Object value) {
        addCondition(field, value, ">", false, true, AND);
        return this;
    }

    /**
     * <p>Description: 大于（>）</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public WhereBuilder orGreaterThan(String field, Object value) {
        addCondition(field, value, ">", false, true, OR);
        return this;
    }

    /**
     * <p>Description: 大于等于（>=）</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public WhereBuilder andGreaterEquals(String field, Object value) {
        addCondition(field, value, ">=", false, true, AND);
        return this;
    }

    /**
     * <p>Description: 大于等于（>=）</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public WhereBuilder orGreaterEquals(String field, Object value) {
        addCondition(field, value, ">=", false, true, OR);
        return this;
    }

    /**
     * <p>Description: 小于（<）</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public WhereBuilder andLessThan(String field, Object value) {
        addCondition(field, value, "<", false, true, AND);
        return this;
    }

    /**
     * <p>Description: 小于（<）</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public WhereBuilder orLessThan(String field, Object value) {
        addCondition(field, value, "<", false, true, OR);
        return this;
    }

    /**
     * <p>Description: 小于（<）</p>
     *
     * @param field   字段
     * @param value   值
     * @param isField value是否是字段名称
     * @return this
     */
    public WhereBuilder andLessThan(String field, Object value, boolean isField) {
        addCondition(field, value, "<", isField, true, AND);
        return this;
    }

    /**
     * <p>Description: 小于（<）</p>
     *
     * @param field   字段
     * @param value   值
     * @param isField value是否是字段名称
     * @return this
     */
    public WhereBuilder orLessThan(String field, Object value, boolean isField) {
        addCondition(field, value, "<", isField, true, OR);
        return this;
    }

    /**
     * <p>Description: 小于等于于（<=）</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public WhereBuilder andLessEquals(String field, Object value) {
        addCondition(field, value, "<=", false, true, AND);
        return this;
    }

    /**
     * <p>Description: 全匹配</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public WhereBuilder andFullLike(String field, Object value) {
        if (isEmpty(field, value)) {
            return this;
        }
        addCondition(field, "%" + value + "%", "LIKE", false, true, AND);
        return this;
    }

    /**
     * <p>Description: 全匹配</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public WhereBuilder orFullLike(String field, Object value) {
        if (isEmpty(field, value)) {
            return this;
        }
        addCondition(field, "%" + value + "%", "LIKE", false, true, OR);
        return this;
    }

    /**
     * <p>Description: 左匹配</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public WhereBuilder andLeftLike(String field, Object value) {
        if (isEmpty(field, value)) {
            return this;
        }
        addCondition(field, "%" + value, "LIKE", false, true, AND);
        return this;
    }

    /**
     * <p>Description: 左匹配</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public WhereBuilder orLeftLike(String field, Object value) {
        if (isEmpty(field, value)) {
            return this;
        }
        addCondition(field, "%" + value, "LIKE", false, true, OR);
        return this;
    }

    /**
     * <p>Description: 右匹配</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public WhereBuilder andRightLike(String field, Object value) {
        if (isEmpty(field, value)) {
            return this;
        }
        addCondition(field, value + "%", "LIKE", false, true, AND);
        return this;
    }

    /**
     * <p>Description: 右匹配</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public WhereBuilder orRightLike(String field, Object value) {
        if (isEmpty(field, value)) {
            return this;
        }
        addCondition(field, value + "%", "LIKE", false, true, OR);
        return this;
    }

    /**
     * <p>Description: in条件</p>
     *
     * @param field 字段
     * @param value 值:使用英文逗号分隔的字符串，如："str1, str2, str3"
     * @return this
     */
    public WhereBuilder andIn(String field, String value) {
        if (isEmpty(field, value))
            return this;

        addCondition(field, value.split(","), "IN", false, true, AND);
        return this;
    }

    /**
     * <p>Description: in条件</p>
     *
     * @param field 字段
     * @param value 值:使用英文逗号分隔的字符串，如："str1, str2, str3"
     * @return this
     */
    public WhereBuilder orIn(String field, String value) {
        if (isEmpty(field, value))
            return this;

        addCondition(field, value.split(","), "IN", false, true, OR);
        return this;
    }

    /**
     * <p>Description: in条件</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public <T> WhereBuilder andIn(String field, T[] value) {
        if (isEmpty(field) || value == null || value.length == 0)
            return this;

        addCondition(field, value, "IN", false, true, AND);
        return this;
    }

    /**
     * <p>Description: in条件</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public <T> WhereBuilder orIn(String field, T[] value) {
        if (isEmpty(field) || value == null || value.length == 0)
            return this;

        addCondition(field, value, "IN", false, true, OR);
        return this;
    }

    /**
     * <p>Description: in条件</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public <T> WhereBuilder andIn(String field, List<T> value) {
        if (isEmpty(field) || value == null || value.isEmpty())
            return this;

        addCondition(field, value.toArray(), "IN", false, true, AND);
        return this;
    }

    /**
     * <p>Description: in条件</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public <T> WhereBuilder orIn(String field, List<T> value) {
        if (isEmpty(field) || value == null || value.isEmpty())
            return this;

        addCondition(field, value.toArray(), "IN", false, true, OR);
        return this;
    }

    /**
     * <p>Description: not in条件</p>
     *
     * @param field 字段
     * @param value 值:使用英文逗号分隔的字符串，如："str1, str2, str3"
     * @return this
     */
    public WhereBuilder andNotIn(String field, String value) {
        if (isEmpty(field, value))
            return this;

        addCondition(field, value.split(","), "NOT IN", false, true, AND);
        return this;
    }

    /**
     * <p>Description: not in条件</p>
     *
     * @param field 字段
     * @param value 值:使用英文逗号分隔的字符串，如："str1, str2, str3"
     * @return this
     */
    public WhereBuilder orNotIn(String field, String value) {
        if (isEmpty(field, value))
            return this;

        addCondition(field, value.split(","), "NOT IN", false, true, OR);
        return this;
    }

    /**
     * <p>Description: not in条件</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public <T> WhereBuilder andNotIn(String field, T[] value) {
        if (isEmpty(field) || value == null || value.length == 0)
            return this;

        addCondition(field, value, "NOT IN", false, true, AND);
        return this;
    }

    /**
     * <p>Description: not in条件</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public <T> WhereBuilder orNotIn(String field, T[] value) {
        if (isEmpty(field) || value == null || value.length == 0)
            return this;

        addCondition(field, value, "NOT IN", false, true, OR);
        return this;
    }

    /**
     * <p>Description: not in条件</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public <T> WhereBuilder andNotIn(String field, List<T> value) {
        if (isEmpty(field) || value == null || value.isEmpty())
            return this;

        addCondition(field, value.toArray(), "NOT IN", false, true, AND);
        return this;
    }

    /**
     * <p>Description: not in条件</p>
     *
     * @param field 字段
     * @param value 值
     * @return this
     */
    public <T> WhereBuilder orNotIn(String field, List<T> value) {
        if (isEmpty(field) || value == null || value.isEmpty())
            return this;

        addCondition(field, value.toArray(), "NOT IN", false, true, OR);
        return this;
    }

    /**
     * and操作符连接的自定义SQL
     *
     * @param sql    自定义SQL
     * @param values 自定义SQL中占位符的值
     * @return this
     */
    public WhereBuilder andCustomSQL(String sql, Object[] values) {
        addCustomSQL(AND, sql, values);
        return this;
    }

    /**
     * or操作符连接的自定义SQL
     *
     * @param sql    自定义SQL
     * @param values 自定义SQL中占位符的值
     * @return this
     */
    public WhereBuilder orCustomSQL(String sql, Object[] values) {
        addCustomSQL(OR, sql, values);
        return this;
    }

    /**
     * 无操作符连接的自定义SQL
     *
     * @param sql    自定义SQL
     * @param values 自定义SQL中占位符的值
     * @return this
     */
    public WhereBuilder setCustomSQL(String sql, Object[] values) {
        addCustomSQL("", sql, values);
        return this;
    }

    public String getOrderBy() {
        return orderBy;
    }

    public void setOrderBy(String orderBy) {
        this.orderBy = orderBy;
    }

    public String getGroupBy() {
        return groupBy;
    }

    public void setGroupBy(String groupBy) {
        this.groupBy = groupBy;
    }

    public int getPageNum() {
        return pageNum;
    }

    public void setPageNum(int pageNum) {
        this.pageNum = pageNum;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    private class Group {
        boolean isWithBracket = false;//是否带括号
        String operator = "";//操作符 and、or
        List<Condition> list;

        /**
         * <p>Description: or条件构造方法</p>
         *
         * @param list 查询条件集合
         */
        public Group(List<Condition> list) {
            this.list = list;
        }

        /**
         * <p>Description: or条件构造方法</p>
         *
         * @param list          查询条件集合
         * @param isWithBracket 是否含有括号
         */
        public Group(List<Condition> list, boolean isWithBracket, String operator) {
            this.list = list;
            this.isWithBracket = isWithBracket;
            this.operator = operator;
        }

        @Override
        public String toString() {
            return super.toString();
        }
    }

    private class Condition {
        String field;
        Object value;
        String clause;
        boolean isField;
        boolean isIndex;
        String operator;

        /**
         * <p>Description: 条件对象构造方法</p>
         *
         * @param field   字段
         * @param value   值
         * @param clause  表达式
         * @param isField 是否字段名称
         */
        public Condition(String field, Object value, String clause, boolean isField, boolean isIndex, String operator) {
            this.field = field;
            this.value = value;
            this.clause = clause;
            this.isField = isField;
            this.isIndex = isIndex;
            this.operator = operator;
        }
    }

    /**
     * 自定义SQL
     */
    private class CustomSQL {
        String operator = "";//操作符 and、or
        String sql;
        Object[] values;

        public CustomSQL(String operator, String sql, Object[] values) {
            this.operator = operator;
            this.sql = sql;
            this.values = values;
        }
    }
    
    /*public static void main(String[] args) {
        QueryCondition qc = new QueryCondition();
		qc.setOrderBy("CC.CREATE_TIME DESC");
		
		qc.andEquals("CC.CATEGORY", 1)
		  .andCustomSQL("CC.ORG_ID IN (SELECT ID FROM TS_ORGANIZATION WHERE ID_PATH LIKE ?)", new Object[]{"orgid22222"});
					  
		qc.andGroup()
		  .andEquals("CC.VALID_STATE", 3)
		  .orEquals("CC.VALID_STATE", 2);
		
		System.out.println(qc.getSql("select * from tablename"));
	}*/
}
